/* Table 2 Panel A Immigration across regions */
clear all
/* cd "M:\ImmPanelRevis17\donnees\cells\"*/
use "C:\Users\Public\Documents\ImmPanelRevis19\ImmPanelRevis19\Donnees\indivm.dta", clear

/**************************************/
/* Définition des zones géographiques */
/**************************************/
/**************************************/
/* 1) => Merge avec les ZE 			  */
/**************************************/
/* elimine la corse */
drop if dep=="2A" | dep=="2B"

gen cgeo=dep+comt
sort cgeo
/* Paris */
replace cgeo="75056" if dep=="75"
/* Lyon */
replace cgeo="69123" if cgeo=="69381" | cgeo=="69382" | cgeo=="69383" | cgeo=="69384" | cgeo=="69385" | cgeo=="69386" | cgeo=="69387" | cgeo=="69388" | cgeo=="69389" 
/* Marseille */
replace cgeo="13055" if cgeo=="13201" | cgeo=="13202" | cgeo=="13203" | cgeo=="13204" | cgeo=="13205" | cgeo=="13206" | cgeo=="13207" | cgeo=="13208" | cgeo=="13209" | cgeo=="13210" | cgeo=="13211" | cgeo=="13212" | cgeo=="13213" | cgeo=="13214" | cgeo=="13215" | cgeo=="13216"

/* définit les ZE */
joinby cgeo using "C:\Users\Public\Documents\ImmPanelRevis19\ImmPanelRevis19\Donnees\cgeo_ze.dta", unm(m)
tab _merge
drop if _merge==1
drop zen

/* foreign born workers */
gen fborn=(depnai=="99")
gen ifborn="N" if fborn==0
replace ifborn="FB" if fborn==1
compress

drop if age<20
drop if age>55

/* all workers */
gen all=1
/* blue collar dummy */
gen ouv=(cs2h=="62" | cs2h=="63" | cs2h=="64" | cs2h=="65" | cs2h=="67" | cs2h=="68" | cs2h=="69" )
/* BLUE COLLARD construction */
gen constbc=(nap40=="24" & ouv==1)
/* BLUE COLLARD tradable */
gen trad=((nap40=="01" | nap40=="02" | ///
nap40=="03" | nap40=="04" | nap40=="05" | nap40=="06" | ///
nap40=="07" | nap40=="08" | nap40=="09" | nap40=="10" | ///
nap40=="11" | nap40=="12" | nap40=="13" | nap40=="14" | ///
nap40=="15" | nap40=="16" | nap40=="17" | nap40=="18" | ///
nap40=="19" | nap40=="20" | nap40=="21" | nap40=="22" | ///
nap40=="23" | nap40=="36" | nap40=="37" | nap40=="35" | nap40=="32") & ouv==1)
gen ntrad=(( nap40=="25" | ///
nap40=="26" | nap40=="27" | nap40=="28" | nap40=="29" | ///
nap40=="30" | nap40=="31" | nap40=="33" | nap40=="34") & ouv==1)
/* eliminate services non-marchand */
gen bc=(nap40!="38" & ouv==1)

/****************************************/
/* ouvriers qualitifés et non qualifiés */
/****************************************/
gen qual=(cs2_anc=="60" | cs2_anc=="61" ///
| cs2=="62" | cs2=="63"| cs2=="64"| cs2=="65")
/* low skilled BLUE COLLARD */
gen bcnq=bc*(1-qual)
gen constbcnq=constbc*(1-qual)
gen tradnq=trad*(1-qual)
gen ntradnq=ntrad*(1-qual)
/* Skilled BLUE COLLARD */
gen bcq=bc*(qual)
gen constbcq=constbc*(qual)
gen tradq=trad*(qual)
gen ntradq=ntrad*(qual)

/* Employees */
gen emp=(cs2h=="54" | cs2h=="55" | cs2h=="56")
/* Employees in the service sector */
gen empsc=(cs2h=="56")

/* Managers */
gen cad=(cs2h=="23" | cs2h=="31" | cs2h=="33" | cs2h=="34" | cs2h=="35" | cs2h=="37" | cs2h=="38")
/* Technicians */
gen tec=(cs2h=="42" | cs2h=="43" | cs2h=="44" | cs2h=="45" | cs2h=="46" | cs2h=="47" )

/* clerk */
gen clrk=(cs2h=="46")

gen rze=substr(ze,1,2)
gen paris=(cgeo=="75056")
gen lyon=(cgeo=="69123")
gen britt=(rze=="53")

drop dep
drop _merge
joinby cgeo using "C:\Users\Public\Documents\ImmPanelRevis19\ImmPanelRevis19\Donnees\cgeo_reg_dep.dta", unm(m)

/* occupations */
gen occup="bcq" if bcq
replace occup="bcnq" if bcnq
replace occup="cad" if cad
replace occup="tec/clrk" if tec | clrk
tab cs2h occup, m

/* TABLE 2 PANEL C */
drop if missing(occup)
/* share natives in the group */
tab occup if !fborn & an==2007
/* share foreign born in the group */
tab occup if fborn & an==2007

keep if an==2007
gen lsnd=ln(sn/dp)

/* TABLE A2 Supplementary Appendix */
save temp2.dta, replace
/* average wage gap with native wrt daily wage => Main Table */
bysort occup: reg lsnd fborn 
/* Share of foreign born workers within occupation groups */
foreach occup in cad tec/clrk bcq bcnq{
use temp2.dta, clear
keep if occup=="`occup'"
fastxtile psnd = lsnd , n(10)
fastxtile qsnd = lsnd , n(4)
display "`occup'"
tab fborn psnd , col
tab fborn qsnd , col
}
